"""
CSGO K线分析数据管理器
"""
from datetime import datetime, timedelta
from typing import Dict, List, Optional, Any, Union
import json

from sqlalchemy import desc, asc, and_, or_, not_, func
from sqlalchemy.sql.expression import cast
from sqlalchemy.types import String

from com.db.sqlalchemy_database import SQLAlchemyDatabaseManager
from com.db.models import CSGOKlineAnalysis


class CSGOKlineAnalysisManager:
    """CSGO K线分析数据管理器"""
    
    def __init__(self):
        """初始化管理器"""
        self.db = SQLAlchemyDatabaseManager()
    
    def add_analysis(self, analysis_data: Dict[str, Any]) -> Optional[Dict[str, Any]]:
        """添加K线分析记录
        
        Args:
            analysis_data: 分析数据字典，必须包含item_id、item_name、analysis_period和current_price字段
            
        Returns:
            添加的分析记录字典，如果添加失败则返回None
        """
        # 设置数据保留期限
        if 'expiry_date' not in analysis_data:
            period = analysis_data.get('analysis_period', 'combined')
            analysis_data['expiry_date'] = self._calculate_expiry_date(period)
        
        # 创建分析记录对象
        analysis = CSGOKlineAnalysis(**analysis_data)
        
        # 添加到数据库
        self.db.add(analysis)
        
        # 返回添加的记录
        return self._convert_model_to_dict(analysis) if analysis else None
    
    def get_analysis_by_id(self, analysis_id: int) -> Optional[Dict[str, Any]]:
        """根据ID获取分析记录
        
        Args:
            analysis_id: 分析记录ID
            
        Returns:
            分析记录字典，如果不存在则返回None
        """
        session = self.db.get_session()
        try:
            analysis = session.query(CSGOKlineAnalysis).filter(
                CSGOKlineAnalysis.id == analysis_id
            ).first()
            return self._convert_model_to_dict(analysis) if analysis else None
        finally:
            session.close()
    
    def get_latest_analysis(self, item_id: str, period: str = 'combined') -> Optional[Dict[str, Any]]:
        """获取指定物品最新的分析记录
        
        Args:
            item_id: 物品ID
            period: 分析周期，默认为'combined'
            
        Returns:
            最新的分析记录字典，如果不存在则返回None
        """
        session = self.db.get_session()
        try:
            analysis = session.query(CSGOKlineAnalysis).filter(
                CSGOKlineAnalysis.item_id == item_id,
                CSGOKlineAnalysis.analysis_period == period
            ).order_by(desc(CSGOKlineAnalysis.timestamp)).first()
            return self._convert_model_to_dict(analysis) if analysis else None
        finally:
            session.close()
    
    def get_latest_analyses(self, period: str = 'combined', limit: int = 100) -> List[Dict[str, Any]]:
        """获取最新的分析记录列表
        
        Args:
            period: 分析周期，默认为'combined'
            limit: 返回记录数量限制，默认为100
            
        Returns:
            最新的分析记录字典列表
        """
        session = self.db.get_session()
        try:
            analyses = session.query(CSGOKlineAnalysis).filter(
                CSGOKlineAnalysis.analysis_period == period
            ).order_by(desc(CSGOKlineAnalysis.timestamp)).limit(limit).all()
            return [self._convert_model_to_dict(analysis) for analysis in analyses]
        finally:
            session.close()
    
    def get_analyses_by_item(self, item_id: str, period: str = 'combined', limit: int = 30) -> List[Dict[str, Any]]:
        """获取指定物品的分析记录列表
        
        Args:
            item_id: 物品ID
            period: 分析周期，默认为'combined'
            limit: 返回记录数量限制，默认为30
            
        Returns:
            分析记录字典列表
        """
        session = self.db.get_session()
        try:
            analyses = session.query(CSGOKlineAnalysis).filter(
                CSGOKlineAnalysis.item_id == item_id,
                CSGOKlineAnalysis.analysis_period == period
            ).order_by(desc(CSGOKlineAnalysis.timestamp)).limit(limit).all()
            return [self._convert_model_to_dict(analysis) for analysis in analyses]
        finally:
            session.close()
    
    def update_analysis(self, analysis_id: int, update_data: Dict[str, Any]) -> Optional[Dict[str, Any]]:
        """更新分析记录
        
        Args:
            analysis_id: 分析记录ID
            update_data: 更新数据字典
            
        Returns:
            更新后的分析记录字典，如果更新失败则返回None
        """
        session = self.db.get_session()
        try:
            analysis = session.query(CSGOKlineAnalysis).filter(
                CSGOKlineAnalysis.id == analysis_id
            ).first()
            
            if analysis:
                # 更新字段
                for key, value in update_data.items():
                    if hasattr(analysis, key):
                        setattr(analysis, key, value)
                
                # 更新时间戳
                analysis.update_at = datetime.now()
                
                # 提交更改
                session.commit()
                
                return self._convert_model_to_dict(analysis)
            return None
        except Exception as e:
            session.rollback()
            raise e
        finally:
            session.close()
    
    def delete_analysis(self, analysis_id: int) -> bool:
        """删除分析记录
        
        Args:
            analysis_id: 分析记录ID
            
        Returns:
            删除是否成功
        """
        session = self.db.get_session()
        try:
            analysis = session.query(CSGOKlineAnalysis).filter(
                CSGOKlineAnalysis.id == analysis_id
            ).first()
            
            if analysis:
                session.delete(analysis)
                session.commit()
                return True
            return False
        except Exception as e:
            session.rollback()
            raise e
        finally:
            session.close()
    
    def delete_expired_analyses(self) -> int:
        """删除过期的分析记录
        
        Returns:
            删除的记录数量
        """
        session = self.db.get_session()
        try:
            now = datetime.now()
            result = session.query(CSGOKlineAnalysis).filter(
                CSGOKlineAnalysis.expiry_date < now
            ).delete()
            session.commit()
            return result
        except Exception as e:
            session.rollback()
            raise e
        finally:
            session.close()
    
    def get_analyses_by_recommendation(self, recommendation: str, period: str = 'combined', limit: int = 50) -> List[Dict[str, Any]]:
        """根据推荐操作获取分析记录
        
        Args:
            recommendation: 推荐操作，如'buy', 'sell', 'hold'
            period: 分析周期，默认为'combined'
            limit: 返回记录数量限制，默认为50
            
        Returns:
            分析记录字典列表
        """
        session = self.db.get_session()
        try:
            analyses = session.query(CSGOKlineAnalysis).filter(
                CSGOKlineAnalysis.recommendation == recommendation,
                CSGOKlineAnalysis.analysis_period == period
            ).order_by(desc(CSGOKlineAnalysis.investment_score)).limit(limit).all()
            return [self._convert_model_to_dict(analysis) for analysis in analyses]
        finally:
            session.close()
    
    def get_analyses_by_price_range(self, min_price: float, max_price: float, period: str = 'combined', limit: int = 50) -> List[Dict[str, Any]]:
        """根据价格范围获取分析记录
        
        Args:
            min_price: 最低价格
            max_price: 最高价格
            period: 分析周期，默认为'combined'
            limit: 返回记录数量限制，默认为50
            
        Returns:
            分析记录字典列表
        """
        session = self.db.get_session()
        try:
            analyses = session.query(CSGOKlineAnalysis).filter(
                CSGOKlineAnalysis.current_price >= min_price,
                CSGOKlineAnalysis.current_price <= max_price,
                CSGOKlineAnalysis.analysis_period == period
            ).order_by(desc(CSGOKlineAnalysis.timestamp)).limit(limit).all()
            return [self._convert_model_to_dict(analysis) for analysis in analyses]
        finally:
            session.close()
    
    def get_analyses_by_trend(self, trend: str, period: str = 'combined', trend_field: str = 'overall_trend', limit: int = 50) -> List[Dict[str, Any]]:
        """根据趋势获取分析记录
        
        Args:
            trend: 趋势方向，如'bullish', 'bearish', 'neutral'
            period: 分析周期，默认为'combined'
            trend_field: 趋势字段，默认为'overall_trend'，可选'hour_trend', 'day_trend', 'week_trend'或'trend_direction'
            limit: 返回记录数量限制，默认为50
            
        Returns:
            分析记录字典列表
        """
        session = self.db.get_session()
        try:
            # 根据分析周期和趋势字段选择过滤条件
            if period != 'combined' and trend_field == 'overall_trend':
                # 如果不是combined周期，但要查询overall_trend，则使用trend_direction
                filter_condition = CSGOKlineAnalysis.trend_direction == trend
            else:
                # 否则使用指定的趋势字段
                filter_condition = getattr(CSGOKlineAnalysis, trend_field) == trend
            
            analyses = session.query(CSGOKlineAnalysis).filter(
                filter_condition,
                CSGOKlineAnalysis.analysis_period == period
            ).order_by(desc(CSGOKlineAnalysis.timestamp)).limit(limit).all()
            return [self._convert_model_to_dict(analysis) for analysis in analyses]
        finally:
            session.close()
    
    def get_anomaly_analyses(self, period: str = 'combined', limit: int = 50) -> List[Dict[str, Any]]:
        """获取存在异常的分析记录
        
        Args:
            period: 分析周期，默认为'combined'
            limit: 返回记录数量限制，默认为50
            
        Returns:
            分析记录字典列表
        """
        session = self.db.get_session()
        try:
            analyses = session.query(CSGOKlineAnalysis).filter(
                or_(
                    CSGOKlineAnalysis.volume_anomaly == True,
                    CSGOKlineAnalysis.price_anomaly == True,
                    CSGOKlineAnalysis.pattern_anomaly == True
                ),
                CSGOKlineAnalysis.analysis_period == period
            ).order_by(desc(CSGOKlineAnalysis.timestamp)).limit(limit).all()
            return [self._convert_model_to_dict(analysis) for analysis in analyses]
        finally:
            session.close()
    
    def get_analyses_by_liquidity(self, min_score: float, period: str = 'combined', limit: int = 50) -> List[Dict[str, Any]]:
        """根据流动性评分获取分析记录
        
        Args:
            min_score: 最低流动性评分
            period: 分析周期，默认为'combined'
            limit: 返回记录数量限制，默认为50
            
        Returns:
            分析记录字典列表
        """
        session = self.db.get_session()
        try:
            analyses = session.query(CSGOKlineAnalysis).filter(
                CSGOKlineAnalysis.liquidity_score >= min_score,
                CSGOKlineAnalysis.analysis_period == period
            ).order_by(desc(CSGOKlineAnalysis.liquidity_score)).limit(limit).all()
            return [self._convert_model_to_dict(analysis) for analysis in analyses]
        finally:
            session.close()
    
    def get_oversold_analyses(self, rsi_threshold: float = 30.0, period: str = 'combined', rsi_field: str = 'rsi', limit: int = 50) -> List[Dict[str, Any]]:
        """获取RSI超卖的分析记录
        
        Args:
            rsi_threshold: RSI阈值，默认为30.0
            period: 分析周期，默认为'combined'
            rsi_field: RSI字段，默认为'rsi'，可选'rsi_hour', 'rsi_day', 'rsi_week'
            limit: 返回记录数量限制，默认为50
            
        Returns:
            分析记录字典列表
        """
        session = self.db.get_session()
        try:
            # 根据分析周期和RSI字段选择过滤条件
            if period != 'combined' and rsi_field != 'rsi':
                # 如果不是combined周期，但要查询特定周期的RSI，则使用rsi
                filter_condition = CSGOKlineAnalysis.rsi <= rsi_threshold
            else:
                # 否则使用指定的RSI字段
                filter_condition = getattr(CSGOKlineAnalysis, rsi_field) <= rsi_threshold
            
            analyses = session.query(CSGOKlineAnalysis).filter(
                filter_condition,
                CSGOKlineAnalysis.analysis_period == period
            ).order_by(asc(getattr(CSGOKlineAnalysis, rsi_field))).limit(limit).all()
            return [self._convert_model_to_dict(analysis) for analysis in analyses]
        finally:
            session.close()
    
    def get_near_support_analyses(self, threshold_percent: float = 5.0, period: str = 'combined', limit: int = 50) -> List[Dict[str, Any]]:
        """获取接近支撑位的分析记录
        
        Args:
            threshold_percent: 接近支撑位的百分比阈值，默认为5.0
            period: 分析周期，默认为'combined'
            limit: 返回记录数量限制，默认为50
            
        Returns:
            分析记录字典列表
        """
        session = self.db.get_session()
        try:
            analyses = []
            # 这里需要在Python中进行处理，因为支撑位存储在JSON字段中
            all_analyses = session.query(CSGOKlineAnalysis).filter(
                CSGOKlineAnalysis.analysis_period == period
            ).order_by(desc(CSGOKlineAnalysis.timestamp)).limit(limit * 2).all()
            
            for analysis in all_analyses:
                if self._is_near_support(analysis, threshold_percent):
                    analyses.append(analysis)
                    if len(analyses) >= limit:
                        break
            
            return [self._convert_model_to_dict(analysis) for analysis in analyses]
        finally:
            session.close()
    
    def get_best_investment_opportunities(self, min_score: float = 70.0, period: str = 'combined', limit: int = 20) -> List[Dict[str, Any]]:
        """获取最佳投资机会
        
        Args:
            min_score: 最低投资评分，默认为70.0
            period: 分析周期，默认为'combined'
            limit: 返回记录数量限制，默认为20
            
        Returns:
            分析记录字典列表
        """
        session = self.db.get_session()
        try:
            analyses = session.query(CSGOKlineAnalysis).filter(
                CSGOKlineAnalysis.investment_score >= min_score,
                CSGOKlineAnalysis.recommendation == 'buy',
                CSGOKlineAnalysis.analysis_period == period
            ).order_by(desc(CSGOKlineAnalysis.investment_score)).limit(limit).all()
            return [self._convert_model_to_dict(analysis) for analysis in analyses]
        finally:
            session.close()
    
    def get_price_history(self, item_id: str, period: str = 'day', days: int = 30) -> List[Dict[str, Any]]:
        """获取物品价格历史
        
        Args:
            item_id: 物品ID
            period: 分析周期，默认为'day'
            days: 天数，默认为30
            
        Returns:
            价格历史记录列表
        """
        session = self.db.get_session()
        try:
            start_date = datetime.now() - timedelta(days=days)
            analyses = session.query(CSGOKlineAnalysis).filter(
                CSGOKlineAnalysis.item_id == item_id,
                CSGOKlineAnalysis.analysis_period == period,
                CSGOKlineAnalysis.timestamp >= start_date
            ).order_by(asc(CSGOKlineAnalysis.timestamp)).all()
            
            # 提取价格历史
            price_history = []
            for analysis in analyses:
                price_history.append({
                    'timestamp': analysis.timestamp,
                    'price': analysis.current_price,
                    'volume': analysis.latest_volume if hasattr(analysis, 'latest_volume') else None
                })
            
            return price_history
        finally:
            session.close()
    
    def get_simple_investment_advice(self, item_id: str) -> Optional[Dict[str, Any]]:
        """获取简单投资建议
        
        Args:
            item_id: 物品ID
            
        Returns:
            简单投资建议字典，如果不存在则返回None
        """
        session = self.db.get_session()
        try:
            analysis = session.query(CSGOKlineAnalysis).filter(
                CSGOKlineAnalysis.item_id == item_id,
                CSGOKlineAnalysis.analysis_period == 'combined'
            ).order_by(desc(CSGOKlineAnalysis.timestamp)).first()
            
            if analysis:
                return {
                    'item_id': analysis.item_id,
                    'item_name': analysis.item_name,
                    'current_price': analysis.current_price,
                    'is_worth_investing': analysis.is_worth_investing,
                    'best_buy_price': analysis.best_buy_price,
                    'best_sell_price': analysis.best_sell_price,
                    'simple_advice': analysis.simple_advice,
                    'timestamp': analysis.timestamp
                }
            return None
        finally:
            session.close()
    
    def _convert_model_to_dict(self, model: CSGOKlineAnalysis) -> Dict[str, Any]:
        """将模型对象转换为字典
        
        Args:
            model: 模型对象
            
        Returns:
            字典表示
        """
        if not model:
            return {}
        
        result = {}
        for column in model.__table__.columns:
            value = getattr(model, column.name)
            if isinstance(value, datetime):
                value = value.isoformat()
            result[column.name] = value
        
        return result
    
    def _is_near_support(self, analysis: CSGOKlineAnalysis, threshold_percent: float) -> bool:
        """判断当前价格是否接近支撑位
        
        Args:
            analysis: 分析记录
            threshold_percent: 接近支撑位的百分比阈值
            
        Returns:
            是否接近支撑位
        """
        if not analysis.current_price:
            return False
        
        # 获取支撑位
        support_levels = []
        if analysis.analysis_period == 'combined':
            # 综合分析使用所有周期的支撑位
            for field in ['hour_support', 'day_support', 'week_support']:
                levels = getattr(analysis, field)
                if levels:
                    if isinstance(levels, str):
                        try:
                            levels = json.loads(levels)
                        except:
                            levels = []
                    support_levels.extend(levels)
        else:
            # 单一周期使用当前周期的支撑位
            levels = analysis.support_levels
            if levels:
                if isinstance(levels, str):
                    try:
                        levels = json.loads(levels)
                    except:
                        levels = []
                support_levels = levels
        
        # 检查是否接近任一支撑位
        for support in support_levels:
            if isinstance(support, (int, float)):
                # 计算当前价格与支撑位的差距百分比
                diff_percent = abs(analysis.current_price - support) / support * 100
                if diff_percent <= threshold_percent:
                    return True
        
        return False
    
    def _calculate_expiry_date(self, period: str) -> datetime:
        """计算数据保留期限
        
        Args:
            period: 分析周期
            
        Returns:
            过期时间
        """
        now = datetime.now()
        if period == 'hour':
            # 时K数据保留7天
            return now + timedelta(days=7)
        elif period == 'day':
            # 日K数据保留30天
            return now + timedelta(days=30)
        elif period == 'week':
            # 周K数据保留90天
            return now + timedelta(days=90)
        else:
            # 综合分析结果保留30天
            return now + timedelta(days=30)
